package dm;

import java.sql.CallableStatement;
import java.sql.Connection;

import database.DBConnection;

public class CriteriaDM {

	public void insertCriteria(String categoryId, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.ADD_CRITERIA(?,?)}");
			statement.setString(1,name);
			statement.setString(2,categoryId);
			statement.execute();
			
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	public void updateCriteria(int id, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.UPDATE_CRITERIA(?,?)}");
			statement.setInt(1,id);
			statement.setString(2,name);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	
	public void delCriteria(int id) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.REMOVE_CRITER(?)}");
			statement.setInt(1,id);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	
	
	public void insertSubCriteria(int criteriaId, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.ADD_SUBCRITERIA(?,?)}");
			statement.setString(1,name);
			statement.setInt(2,criteriaId);
			statement.execute();
			
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	public void updateSubCriteria(int id, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.UPDATE_SUBCRITERIA(?,?)}");
			statement.setInt(1,id);
			statement.setString(2,name);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	
	public void delSubCriteria(int id) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.REMOVE_SUBCRITER(?)}");
			statement.setInt(1,id);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}
	
	public void addProductToSubCriteria(int subCriteriaId, int productId) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CRITERIA_PKG.ADD_SUBCRITER_PRODUCT(?,?)}");
			statement.setInt(1,subCriteriaId);
			statement.setInt(2,productId);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}
}
